﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_GetMemberByConfirmationGuid')
BEGIN
    PRINT 'Dropping Procedure proc_cm_GetMemberByConfirmationGuid'
    DROP  Procedure  proc_cm_GetMemberByConfirmationGuid
END
GO

PRINT 'Creating Procedure proc_cm_GetMemberByConfirmationGuid'
GO

CREATE PROCEDURE [dbo].[proc_cm_GetMemberByConfirmationGuid]
	@pConfirmationGuid [uniqueidentifier]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

	SELECT 
		m.[member_id]
		,m.[UserId]
		,u.[UserName] as [username_txt]
		,mem.[Password] as [password_txt]
		,m.[firstname_txt]
		,m.[lastname_txt]
		,m.[korean_nm]
		,mem.[Email] as [emailaddr_txt]
		,m.[address_txt]
		,m.[address2_txt]
		,m.[city_txt]
		,m.[state_cd]
		,m.[zip_cd]
		,m.[country_nm]
		,m.[homephone_txt]
		,m.[cellphone_txt]
		,m.[birthdate_dt] 
		,m.[icon_filename_txt] 
		,m.[member_since_dt]
		,m.[last_login_dttm]
		,m.[confirmation_guid]
		,m.[created_dttm]
	FROM 
		[dbo].[tbl_member] m (NOLOCK)
		LEFT JOIN [dbo].[aspnet_Users] u (NOLOCK) ON m.[UserId] = u.[UserId]
		LEFT JOIN [dbo].[aspnet_Membership] mem (NOLOCK) ON m.[UserId] = mem.[UserId]
	WHERE
		m.[confirmation_guid] = @pConfirmationGuid

END
GO

GRANT EXEC ON dbo.proc_cm_GetMemberByConfirmationGuid TO PUBLIC
GO

